﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH.App_Code
{
    public class EduForumReply
    {
        public int EF_ID { get; set; }
        public string reply { get; set; }
        public string category { get; set; }
        public string userName { get; set; }
        public DateTime dateTime { get; set; }
        public string imagePath { get; set; }
        public string imageName { get; set; }
        public string title { get; set; }
        public string country { get; set; }

        public static List<EduForumReply> GetEduReply(int EF_ID)
        {
            SqlConnection conn = null;
            List<EduForumReply> result = new List<EduForumReply>();
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Select * from EduForumReply where EF_ID = @EF_ID order by [dateTime] asc";
                comm.Parameters.AddWithValue("@EF_ID", EF_ID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    EduForumReply efr = new EduForumReply();
                    efr.EF_ID = (int)dr["EF_ID"];
                    efr.category = (string)dr["category"];
                    efr.dateTime = (DateTime)dr["dateTime"];
                    efr.userName = (string)dr["userName"];
                    efr.reply = (string)dr["reply"];
                    efr.imageName = (string)dr["imageName"];
                    efr.imagePath = (string)dr["imagePath"];
                    efr.title = (string)dr["title"];
                    efr.country = (string)dr["country"];
                    result.Add(efr);
                }
                dr.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void insertEduReply(string userName, string reply, int EF_ID, DateTime dateTime, string category, string imageName, string imagePath, string title, string country)
        {
            SqlConnection conn = null;
            List<EduForumReply> result = new List<EduForumReply>();
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "insert into EduForumReply(userName, reply, EF_ID, dateTime, category, imagePath, imageName, title, country) VALUES (@userName, @reply, @EF_ID, @dateTime, @category, @imagePath, @imageName, @title, @country)";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@reply", reply);
                comm.Parameters.AddWithValue("@EF_ID", EF_ID);
                comm.Parameters.AddWithValue("@dateTime", dateTime);
                comm.Parameters.AddWithValue("@category", category);
                comm.Parameters.AddWithValue("@imagePath", imagePath);
                comm.Parameters.AddWithValue("@imageName", imageName);
                comm.Parameters.AddWithValue("@title", title);
                comm.Parameters.AddWithValue("@country", country);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changeAvatars(string newImagePath, string newImageName, string userName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update EduForumReply set imagePath = @newImagePath, imageName = @newImageName where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@newImagePath", newImagePath);
                comm.Parameters.AddWithValue("@newImageName", newImageName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

    }
}